﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace ToppingControl.Models
{
   public class TopCordActModel
   {
      public class TopCordAct_DS
      {
         /// <summary>
         /// Header
         /// </summary>
         public string _Topno { get; set; }
         public string _Plandt { get; set; }
         public string _Itemno { get; set; }
         public string _Topcrdcd { get; set; } 
         public string _Tcrdcd { get; set; }
         public string _Tcrdlot { get; set; }
         public string _HLength { get; set; }
         public string _HExp { get; set; }
         public string  _Compound { get; set; }
         public string _ComLot { get; set; }
         public string _ComPlt { get; set; }
         public string _ComExp { get; set; }
         public string _Manufc { get; set; }
         public string _Formula { get; set; }
         public string _Sample { get; set; }
         /// <summary>
         /// Active
         /// </summary>

         public string _Prddate { get; set; }
         public string _Rollno { get; set; }
         public string _Lenght { get; set; }
         public string _Widgth { get; set; }
         public string _Expired { get; set; }
         public string _50ds { get; set; }
         public string _50os { get; set; }
         public string _50wd { get; set; }
         public string _100ds { get; set; }
         public string _100os { get; set; }
         public string _100wd { get; set; }
         public string _200ds { get; set; }
         public string _200os { get; set; }
         public string _200wd { get; set; }
         public string _300ds { get; set; }
         public string _300os { get; set; }
         public string _300wd { get; set; }
         public string _400ds { get; set; }       
         public string _400ms { get; set; }
         public string _400os { get; set; }
         public string _400wd { get; set; }
         public string _StdUsed { get; set; }
         public string _Empname { get; set; }
         public string _Shift { get; set; }
         public string _Update { get; set; }
         public string _Finflag { get; set; }
         public string _Findat { get; set; }
         public string _msg { get; set; }

      }
      public string GET_ROLL(TopCordAct_DS data)
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select isnull(max(rollno),0)+1  mRoll, a.topcrdcd,a.tcrdcd,a.tcrdlot from ");
         
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a");
         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b ");
         str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
         str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
        
         str.AppendLine(" where  isnull(b.[finflag],'') = 'X' and a.runflag='X' and b.topcrdcd is not null ");
         str.AppendLine(" and Skpflag is null ");
         str.AppendLine(" and a.topcrdcd = @top and a.tcrdcd = @code and a.tcrdlot=@lot and a.itemno=@itemno ");
         if (StrUtil.IsEmpty(data._Prddate))
         {
            str.AppendLine(" and b.prddate=getdate() ");
         }
         else
         {
            str.AppendLine(" and b.prddate=convert(datetime,@prd,120) ");
         }
         str.AppendLine(" group by a.topcrdcd,a.tcrdcd,a.tcrdlot,a.itemno ");

         SqlCommand cmd = new SqlCommand(str.ToString() , cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;


         cmd.Parameters.Add("@top", SqlDbType.VarChar).Value = data._Topcrdcd;
         cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = data._Tcrdcd;
         cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = data._Itemno;
         cmd.Parameters.Add("@lot", SqlDbType.VarChar).Value = data._Tcrdlot;

         if (StrUtil.IsNotEmpty(data._Prddate))
         {
            cmd.Parameters.Add("@prd", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(data._Prddate, WinNemoLib.DateUtil.eLanguage.EN);
         }

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordAct_DS> list = new List<TopCordAct_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return "1";
         }

         return ds.Tables[0].Rows[0]["mRoll"].ToString();
      }

      public List<TopCordAct_DS> GET_NOFINISH(string top = "")
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select " + top);
         str.AppendLine(" a.topno, a.plandt, a.itemno,a.Tcrdcd, a.Tcrdlot,a.topcrdcd ,a.manufc ,a.length");
         str.AppendLine(" , isnull(stdform,0) stdform,[stdused] from ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a");
         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b ");
         str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
         str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM m on a.topcrdcd=m.topcrdcd and a.tcrdcd=m.tcrdcd ");
         str.AppendLine(" where  isnull(b.[finflag],'') = '' and a.runflag='X' and b.topcrdcd is not null ");
         str.AppendLine(" and Skpflag is null ");
         str.AppendLine(" group by  a.topno, a.plandt, a.itemno,a.Tcrdcd, a.Tcrdlot,a.topcrdcd,a.manufc ,a.length , [stdused],isnull(stdform,0) ");

         SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordAct_DS> list = new List<TopCordAct_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TopCordAct_DS model = new TopCordAct_DS();
            model._Topno = items["topno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Manufc = items["manufc"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._HLength = items["length"].ToString();
            model._Tcrdcd = items["Tcrdcd"].ToString();
            model._Tcrdlot = items["Tcrdlot"].ToString();
            model._StdUsed = items["stdused"].ToString();
            model._Formula = items["stdform"].ToString();
        
            list.Add(model);
         }
         return list;
      }
      public List<TopCordAct_DS> SEL_ACT_FORTOP(string top = "")
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select " + top);
         str.AppendLine(" a.topno, a.plandt, a.itemno,a.Tcrdcd, a.Tcrdlot,a.topcrdcd ,a.manufc ,a.length ");
         str.AppendLine(" ,a.tcrdexp,a.compcd ,Complot,Comppllt,Compexp,a.empname,  prddate ");
         str.AppendLine("  from ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a");
         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b ");
         str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
         str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM m on a.topcrdcd=m.topcrdcd and a.tcrdcd=m.tcrdcd ");
         str.AppendLine(" where  isnull(b.[finflag],'') = '' and a.runflag='X' ");
         str.AppendLine(" and Skpflag is null ");

         str.AppendLine(" group by  a.topno, a.plandt, a.itemno,a.Tcrdcd, a.Tcrdlot,a.topcrdcd,a.manufc ,a.length, prddate ");
         str.AppendLine(" ,a.tcrdexp,a.compcd ,Complot,Comppllt,Compexp ,a.empname");
         SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordAct_DS> list = new List<TopCordAct_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TopCordAct_DS model = new TopCordAct_DS();
            model._Topno = items["topno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Manufc = items["manufc"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._HLength = items["length"].ToString();
            model._Tcrdcd = items["Tcrdcd"].ToString();
            model._Tcrdlot = items["Tcrdlot"].ToString();
            model._HExp = items["tcrdexp"].ToString();
            model._Compound = items["compcd"].ToString();
            model._ComLot = items["Complot"].ToString();
            model._ComPlt = items["Comppllt"].ToString();
            model._ComExp =  DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
            model._Empname = items["empname"].ToString();
            model._Prddate = (StrUtil.IsEmpty(items["prddate"].ToString()) ? "" : DateUtil.ToDisplayDate(items["prddate"], DateUtil.eLanguage.EN));
            list.Add(model);
         }
         return list;
      }



      //string topcode , string cust , string tirelot,string rFrm , string rTo
      public List<TopCordAct_DS> GET_ACT_FORTOP(string top = "", string topcode = ""
               , string cust = "", string tirelot = "", string rFrm = "", string rTo = "", string dPrd = "", string tcrdcd="")
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select " + top);
         str.AppendLine(" a.*, isnull(prddate,getdate()) prddate,");
         str.AppendLine(" b.[length] b_len, b.[width] b_width,  isnull(b.[expired],dateadd(Day,m.[age]+1,getdate())) b_exp ");
         str.AppendLine(" , [ds50], [os50], [wd50], [ds100], [os100], [wd100], [ds200]");
         str.AppendLine(" , [os200], [wd200], [ds300], [os300], [wd300], [ds400], [ms400]");
         str.AppendLine(" , [os400], [wd400]");
         str.AppendLine(" , isnull([stdform],0) stdform,stdused,rollno ");
         str.AppendLine(" , b.[empname] emp from");

         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a");
         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b ");
         str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
         str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM m on a.topcrdcd=m.topcrdcd and a.tcrdcd=m.irccrdcd ");
         str.AppendLine(" where  isnull(b.[finflag],'') = '' and a.runflag='X' ");

         if (StrUtil.IsNotEmpty(topcode)){
            str.AppendLine(" and a.topcrdcd = @code ");
         }

  
         if (StrUtil.IsNotEmpty(topcode)){
            str.AppendLine(" and a.Tcrdcd = @tcrdcd ");
         }       if (StrUtil.IsNotEmpty(cust))
         {
            str.AppendLine(" and a.manufc = @cust ");
         }
         if (StrUtil.IsNotEmpty(tirelot))
         {
            str.AppendLine(" and a.Tcrdlot = @Tcrdlot ");
         }

       if (StrUtil.IsNotEmpty(dPrd))
         {
            str.AppendLine(" and prddate =convert(datetime, @prddt ,120) ");
         }
         if (StrUtil.IsNotEmpty(rFrm) && StrUtil.IsNotEmpty(rTo))
         {
            str.AppendLine(" and rollno between @frm and @rTo ");
         }
         else if (StrUtil.IsNotEmpty(rFrm))
         {
            str.AppendLine(" and rollno = @frm  ");
         }


         SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno,rollno desc ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(dPrd))
         {
            cmd.Parameters.Add("@prddt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dPrd, WinNemoLib.DateUtil.eLanguage.EN);
         }
         if (StrUtil.IsNotEmpty(topcode))
         {
            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;
         } if (StrUtil.IsNotEmpty(tcrdcd))
         {
            cmd.Parameters.Add("@tcrdcd", SqlDbType.VarChar).Value = tcrdcd;
         }
         if (StrUtil.IsNotEmpty(cust))
         {
            cmd.Parameters.Add("@cust", SqlDbType.VarChar).Value = cust;
         }
         if (StrUtil.IsNotEmpty(tirelot))
         {
            cmd.Parameters.Add("@Tcrdlot", SqlDbType.VarChar).Value = tirelot;
         }
         if (StrUtil.IsNotEmpty(rTo))
         {
            cmd.Parameters.Add("@rTo", SqlDbType.VarChar).Value =rTo;
           
         }
          if (StrUtil.IsNotEmpty(rFrm))
         {
            cmd.Parameters.Add("@frm", SqlDbType.VarChar).Value =rFrm;
         }


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordAct_DS> list = new List<TopCordAct_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TopCordAct_DS model = new TopCordAct_DS();
            model._Topno = items["topno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Manufc = items["manufc"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._HLength = items["length"].ToString();
            model._Tcrdcd = items["Tcrdcd"].ToString();
            model._Tcrdlot = items["Tcrdlot"].ToString();
            model._HExp = DateUtil.ToDisplayDate(items["tcrdexp"], DateUtil.eLanguage.EN);
            model._Compound = items["compcd"].ToString();
            model._ComLot = items["Complot"].ToString();
            model._ComPlt = items["Comppllt"].ToString();
            model._ComExp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
            model._StdUsed = items["stdused"].ToString();
            model._Shift = items["shift"].ToString();
            model._Prddate = DateUtil.ToDisplayDate(items["prddate"], DateUtil.eLanguage.EN);
            model._Rollno = items["rollno"].ToString();
            model._Lenght = items["b_len"].ToString();
            model._Widgth = items["b_width"].ToString();
            model._Expired = DateUtil.ToDisplayDate(items["b_exp"], DateUtil.eLanguage.EN);
            model._50ds = items["ds50"].ToString();
            model._50os = items["os50"].ToString();
            model._50wd = items["wd50"].ToString();
            model._100ds = items["ds100"].ToString();
            model._100os = items["os100"].ToString();
            model._100wd = items["wd100"].ToString();
            model._200ds = items["ds200"].ToString();
            model._200os = items["os200"].ToString();
            model._200wd = items["wd200"].ToString();
            model._300ds = items["ds300"].ToString();
            model._300os = items["os300"].ToString();
            model._300wd = items["wd300"].ToString();
            model._400ds = items["ds400"].ToString();
            model._400ms = items["ms400"].ToString();
            model._400os = items["os400"].ToString();
            model._400wd = items["wd400"].ToString();
            model._Formula = items["stdform"].ToString();
            model._Sample = items["sample"].ToString();
            list.Add(model);
         }
         return list;
      }

      public List<TopCordAct_DS> LIST_ACT_FORTOP( string topcode = ""
              , string cust = "", string tirelot = "", string rFrm = "", string rTo = "", string dPrd = "")
      {
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();

         str.AppendLine("  select distinct ");
         str.AppendLine(" a.*, isnull(prddate,getdate()) prddate,b.shift");
         str.AppendLine(" ,b.[length] b_len, b.[width] b_width,  isnull(b.[expired],dateadd(Day,c.[age]+2,getdate())) b_exp ");
         str.AppendLine(" , [ds50], [os50], [wd50], [ds100], [os100], [wd100], [ds200]");
         str.AppendLine(" , [os200], [wd200], [ds300], [os300], [wd300], [ds400], [ms400]");
         str.AppendLine(" , [os400], [wd400]");
         str.AppendLine(" , isnull([stdused],c.stdform) stdused ");
         str.AppendLine(" , b.[empname] emp,rollno from");
     
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b");

         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM c on b.Tcrdcd=c.Tcrdcd ");

         str.AppendLine(" left join ");
         str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a ");
         str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
         str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
         str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM m on a.topcrdcd=m.topcrdcd and a.tcrdcd=m.tcrdcd ");
         str.AppendLine(" where   a.runflag='X' ");
         str.AppendLine(" and Skpflag is null ");

         if (StrUtil.IsNotEmpty(topcode))
         {
            str.AppendLine(" and a.topcrdcd = @code ");
         }

         if (StrUtil.IsNotEmpty(cust))
         {
            str.AppendLine(" and a.manufc = @cust ");
         }
         if (StrUtil.IsNotEmpty(tirelot))
         {
            str.AppendLine(" and a.Tcrdlot = @Tcrdlot ");
         }
         if (StrUtil.IsNotEmpty(dPrd))
         {
            str.AppendLine(" and prddate =convert(datetime, @prddt ,120) ");
         }
         if (StrUtil.IsNotEmpty(rFrm) && StrUtil.IsNotEmpty(rTo))
         {
            str.AppendLine(" and rollno between @frm and @rTo ");
         }
         else if (StrUtil.IsNotEmpty(rFrm))
         {
            str.AppendLine(" and rollno = @frm  ");
         }


         SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno,rollno  ", cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         if (StrUtil.IsNotEmpty(dPrd))
         {
            cmd.Parameters.Add("@prddt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dPrd, WinNemoLib.DateUtil.eLanguage.EN);
         }
         if (StrUtil.IsNotEmpty(topcode))
         {
            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;
         }
         if (StrUtil.IsNotEmpty(cust))
         {
            cmd.Parameters.Add("@cust", SqlDbType.VarChar).Value = cust;
         }
         if (StrUtil.IsNotEmpty(tirelot))
         {
            cmd.Parameters.Add("@Tcrdlot", SqlDbType.VarChar).Value = tirelot;
         }
         if (StrUtil.IsNotEmpty(rTo))
         {
            cmd.Parameters.Add("@rTo", SqlDbType.VarChar).Value = rTo;

         }
         if (StrUtil.IsNotEmpty(rFrm))
         {
            cmd.Parameters.Add("@frm", SqlDbType.VarChar).Value = rFrm;
         }


         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TopCordAct_DS> list = new List<TopCordAct_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TopCordAct_DS model = new TopCordAct_DS();
            model._Topno = items["topno"].ToString();
            model._Itemno = items["itemno"].ToString();
            model._Topcrdcd = items["topcrdcd"].ToString();
            model._Manufc = items["manufc"].ToString();
            model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._HLength = items["length"].ToString();
            model._Tcrdcd = items["Tcrdcd"].ToString();
            model._Tcrdlot = items["Tcrdlot"].ToString();
            model._HExp = DateUtil.ToDisplayDate(items["tcrdexp"], DateUtil.eLanguage.EN);
            model._Compound = items["compcd"].ToString();
            model._ComLot = items["Complot"].ToString();
            model._ComPlt = items["Comppllt"].ToString();
            model._ComExp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
            model._StdUsed = items["stdused"].ToString();
            model._Shift = items["shift"].ToString();
            model._Prddate = DateUtil.ToDisplayDate(items["prddate"], DateUtil.eLanguage.EN);
            model._Rollno = items["rollno"].ToString();
            model._Lenght = items["b_len"].ToString();
            model._Widgth = items["b_width"].ToString();
            model._Expired = DateUtil.ToDisplayDate(items["b_exp"], DateUtil.eLanguage.EN);
            model._50ds = items["ds50"].ToString();
            model._50os = items["os50"].ToString();
            model._50wd = items["wd50"].ToString();
            model._100ds = items["ds100"].ToString();
            model._100os = items["os100"].ToString();
            model._100wd = items["wd100"].ToString();
            model._200ds = items["ds200"].ToString();
            model._200os = items["os200"].ToString();
            model._200wd = items["wd200"].ToString();
            model._300ds = items["ds300"].ToString();
            model._300os = items["os300"].ToString();
            model._300wd = items["wd300"].ToString();
            model._400ds = items["ds400"].ToString();
            model._400ms = items["ms400"].ToString();
            model._400os = items["os400"].ToString();
            model._400wd = items["wd400"].ToString();
            model._Empname = items["empname"].ToString();
            list.Add(model);
         }
         return list;
      }

      public Boolean INS_ACT(TopCordAct_DS incoming)
      {
         try
         {
            using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
            {
               cnn.Open();
               cnn.BeginTransaction();

               if (!DEL_DATA_ACT(cnn, incoming))
               {
                  cnn.RollbackTransaction();
                  return false;
               }

               StringBuilder comstr = new StringBuilder();
               comstr.AppendLine(" INSERT INTO ");
               comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOPCORDACT] ");

               comstr.AppendLine(" ([topno], [plandt], [itemno], [tcrdcd], [tcrdlot], [topcrdcd], [prddate], [rollno], [length], [width], [expired] ");
               comstr.AppendLine(",[ds50], [os50], [wd50], [ds100], [os100], [wd100], [ds200], [os200], [wd200], [ds300], [os300], [wd300]");
               comstr.AppendLine(", [ds400], [ms400], [os400], [wd400]");
  
               comstr.AppendLine(", [stdused], [empname], [shift], [updat] )VALUES(@topno, convert(datetime,@plandt,120)");

               comstr.AppendLine(", @itemno, @tcrdcd, @tcrdlot, @topcrdcd,convert(datetime,@prddate,120)");
               comstr.AppendLine(", @rollno, @length, @width,convert(datetime,@expired,120),");
               comstr.AppendLine(" @ds50, @os50, @wd50, @ds100, @os100, @wd100, @ds200, @os200");

               comstr.AppendLine(", @wd200, @ds300, @os300, @wd300, @ds400, @ms400, @os400, @wd400");

               comstr.AppendLine(", @stdused, @empname,@shift, getdate())");

               CommandWrapper cmd = cnn.CreateCommand();
               cmd.CommandText = comstr.ToString();
               cmd.AddParameterStringWithValue("@topno", incoming._Topno);
               cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
               cmd.AddParameterStringWithValue("@itemno", incoming._Itemno);
               cmd.AddParameterStringWithValue("@topcrdcd", incoming._Topcrdcd);
               cmd.AddParameterStringWithValue("@Tcrdcd", incoming._Tcrdcd);
               cmd.AddParameterStringWithValue("@Tcrdlot", incoming._Tcrdlot);
               cmd.AddParameterStringWithValue("@prddate", DateUtil.ToInternalDate(incoming._Prddate, WinNemoLib.DateUtil.eLanguage.EN));
               cmd.AddParameterStringWithValue("@rollno", incoming._Rollno.Trim());
               cmd.AddParameterStringWithValue("@length", incoming._Lenght);
               cmd.AddParameterStringWithValue("@width", incoming._Widgth);
               cmd.AddParameterStringWithValue("@expired", DateUtil.ToInternalDate(incoming._Expired, WinNemoLib.DateUtil.eLanguage.EN));

               cmd.AddParameterStringWithValue("@ds50", incoming._50ds);
               cmd.AddParameterStringWithValue("@os50", incoming._50os);
               cmd.AddParameterStringWithValue("@wd50", incoming._50wd);
               cmd.AddParameterStringWithValue("@ds100", incoming._100ds);
               cmd.AddParameterStringWithValue("@os100", incoming._100os);
               cmd.AddParameterStringWithValue("@wd100", incoming._100wd);
               cmd.AddParameterStringWithValue("@ds200", incoming._200ds);
               cmd.AddParameterStringWithValue("@os200", incoming._200os);
               cmd.AddParameterStringWithValue("@wd200", incoming._200wd);
               cmd.AddParameterStringWithValue("@ds300", incoming._300ds);
               cmd.AddParameterStringWithValue("@os300", incoming._300os);
               cmd.AddParameterStringWithValue("@wd300", incoming._300wd);
               cmd.AddParameterStringWithValue("@ds400", incoming._400ds);
               cmd.AddParameterStringWithValue("@ms400", incoming._400ms);
               cmd.AddParameterStringWithValue("@os400", incoming._400os);
               cmd.AddParameterStringWithValue("@wd400", incoming._400wd);
               
               cmd.AddParameterStringWithValue("@stdused", incoming._StdUsed);
               cmd.AddParameterStringWithValue("@shift", incoming._Shift);               
               cmd.AddParameterStringWithValue("@empname", incoming._Empname);


               if (cmd.ExecuteNonQuery() <= 0)
               {
                  cnn.RollbackTransaction();
                  return false;
               }

               if (StrUtil.IsNotEmpty(incoming._Finflag))
               {
                  if (!UPD_FIN_ACT(cnn, incoming))
                  {
                     cnn.RollbackTransaction();
                     return false;
                  }
               }

               cnn.CommitTransaction();
               return true;
            }
         }
         catch (Exception ex)
         {
            Nemo.Error(ex);
            return false;
         }

      }

      public Boolean UPD_FIN_ACT(ConnectionWrapper cnn, TopCordAct_DS incoming)
      {
         try
         {
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" UPDATE ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOPCORDACT]");
            comstr.AppendLine(" SET finflag='X' , findat=getdate() ");
            comstr.AppendLine(" where topno=@topno and plandt=convert(datetime,@plandt,120) and itemno=@itemno ");
            comstr.AppendLine(" and Tcrdcd=@tcrdcd and Tcrdlot=@tcrdlot and topcrdcd=@topcrdcd and prddate=convert(datetime,@prddate,120) ");

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();

            cmd.AddParameterStringWithValue("@topno", incoming._Topno);
            cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@itemno", incoming._Itemno);
            cmd.AddParameterStringWithValue("@topcrdcd", incoming._Topcrdcd);
            cmd.AddParameterStringWithValue("@tcrdcd", incoming._Tcrdcd);
            cmd.AddParameterStringWithValue("@tcrdlot", incoming._Tcrdlot);
            cmd.AddParameterStringWithValue("@prddate", DateUtil.ToInternalDate(incoming._Prddate, WinNemoLib.DateUtil.eLanguage.EN));            

            cmd.ExecuteNonQuery();

            return true;
         }
         catch (Exception ex)
         {
            return false;
            throw ex;
         }

      }

      public Boolean DEL_DATA_ACT(ConnectionWrapper cnn, TopCordAct_DS incoming)
      {
         try
         {
            StringBuilder comstr = new StringBuilder();
            comstr.AppendLine(" DELETE ");
            comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[TOPCORDACT]");
            comstr.AppendLine("where topno=@topno and plandt=convert(datetime,@plandt,120) and itemno=@itemno ");
            comstr.AppendLine(" and Tcrdcd=@tcrdcd and Tcrdlot=@tcrdlot and topcrdcd=@topcrdcd and prddate=convert(datetime,@prddate,120) and rollno=@rollno ");

            CommandWrapper cmd = cnn.CreateCommand();
            cmd.CommandText = comstr.ToString();

            cmd.AddParameterStringWithValue("@topno", incoming._Topno);
            cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._Plandt, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@itemno", incoming._Itemno);
            cmd.AddParameterStringWithValue("@topcrdcd", incoming._Topcrdcd);
            cmd.AddParameterStringWithValue("@tcrdcd", incoming._Tcrdcd);
            cmd.AddParameterStringWithValue("@tcrdlot", incoming._Tcrdlot);
            cmd.AddParameterStringWithValue("@prddate", DateUtil.ToInternalDate(incoming._Prddate, WinNemoLib.DateUtil.eLanguage.EN));
            cmd.AddParameterStringWithValue("@rollno", incoming._Rollno);

            cmd.ExecuteNonQuery();

            return true;
         }
         catch (Exception ex)
         {
            return false;
            throw ex;
         }

      }

      public string GET_ROLLNO(string plndt,string itemno)
      {
          
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine(" select isnull(max(rollno),0) rollno from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT ");
          str.AppendLine(" where plandt=convert(datetime,@dTo,120) and itemno=@itemno  ");
          str.AppendLine("  group by plandt,itemno,topno ");
          str.AppendLine(" having isnull(max(rollno),0)+1 not in (select rollno from " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORD_CMPCHG ");
          str.AppendLine(" where plandt=convert(datetime,@dTo,120) and itemno=@itemno ) ");

          SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;
          cmd.Parameters.Add("@dTo", SqlDbType.NVarChar).Value = DateUtil.ToInternalDate(plndt, WinNemoLib.DateUtil.eLanguage.EN);
          cmd.Parameters.Add("@itemno", SqlDbType.NVarChar).Value = itemno;

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return "";
          }
          return ds.Tables[0].Rows[0][0].ToString();
           
      }

      public List<TopCordAct_DS> LIST_FOR_PRINT(string topcode = ""
           , string cust = "", string tirelot = "", string rFrm = "", string rTo = "", string dPrd = "")
      {
          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();

          str.AppendLine("  select distinct ");
          str.AppendLine(" a.*, isnull(prddate,getdate()) prddate,b.shift");
          str.AppendLine(" ,b.[length] b_len, b.[width] b_width,  isnull(b.[expired],dateadd(Day,c.[age]+2,getdate())) b_exp ");
          str.AppendLine(" , [ds50], [os50], [wd50], [ds100], [os100], [wd100], [ds200]");
          str.AppendLine(" , [os200], [wd200], [ds300], [os300], [wd300], [ds400], [ms400]");
          str.AppendLine(" , [os400], [wd400]");
          str.AppendLine(" , isnull([stdused],c.stdform) stdused ");
          str.AppendLine(" , b.[empname] emp,rollno from");
          str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDPLAN a ");


          str.AppendLine(" left join ");
          str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM c on a.tcrdcd=c.irccrdcd "); 
          str.AppendLine(" left join ");
          str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDACT b");       
          str.AppendLine(" on a.topno=b.topno and a.plandt=b.plandt and a.itemno=b.itemno ");
          str.AppendLine(" and a.Tcrdcd=b.Tcrdcd and a.Tcrdlot=b.Tcrdlot and a.topcrdcd=b.topcrdcd ");
          //str.AppendLine(" left join " + Nemo.GetINI("Dbase", "Base") + ".dbo.TOPCORDBOM m on a.topcrdcd=m.topcrdcd and a.tcrdcd=m.tcrdcd ");
          str.AppendLine(" where   a.runflag='X' ");
          str.AppendLine(" and Skpflag is null ");

          if (StrUtil.IsNotEmpty(topcode))
          {
              str.AppendLine(" and a.topcrdcd = @code ");
          }

          if (StrUtil.IsNotEmpty(cust))
          {
              str.AppendLine(" and a.manufc = @cust ");
          }
          if (StrUtil.IsNotEmpty(tirelot))
          {
              str.AppendLine(" and a.Tcrdlot = @Tcrdlot ");
          }
          if (StrUtil.IsNotEmpty(dPrd))
          {
              str.AppendLine(" and prddate =convert(datetime, @prddt ,120) ");
          }
          if (StrUtil.IsNotEmpty(rFrm) && StrUtil.IsNotEmpty(rTo))
          {
              str.AppendLine(" and rollno between @frm and @rTo ");
          }
          else if (StrUtil.IsNotEmpty(rFrm))
          {
              str.AppendLine(" and rollno = @frm  ");
          }


          SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,a.itemno,rollno  ", cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          if (StrUtil.IsNotEmpty(dPrd))
          {
              cmd.Parameters.Add("@prddt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dPrd, WinNemoLib.DateUtil.eLanguage.EN);
          }
          if (StrUtil.IsNotEmpty(topcode))
          {
              cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;
          }
          if (StrUtil.IsNotEmpty(cust))
          {
              cmd.Parameters.Add("@cust", SqlDbType.VarChar).Value = cust;
          }
          if (StrUtil.IsNotEmpty(tirelot))
          {
              cmd.Parameters.Add("@Tcrdlot", SqlDbType.VarChar).Value = tirelot;
          }
          if (StrUtil.IsNotEmpty(rTo))
          {
              cmd.Parameters.Add("@rTo", SqlDbType.VarChar).Value = rTo;

          }
          if (StrUtil.IsNotEmpty(rFrm))
          {
              cmd.Parameters.Add("@frm", SqlDbType.VarChar).Value = rFrm;
          }


          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<TopCordAct_DS> list = new List<TopCordAct_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return list;
          }
          for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
          {
              DataRow items = ds.Tables[0].Rows[cuIndex];
              TopCordAct_DS model = new TopCordAct_DS();
              model._Topno = items["topno"].ToString();
              model._Itemno = items["itemno"].ToString();
              model._Topcrdcd = items["topcrdcd"].ToString();
              model._Manufc = items["manufc"].ToString();
              model._Plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
              model._HLength = items["length"].ToString();
              model._Tcrdcd = items["Tcrdcd"].ToString();
              model._Tcrdlot = items["Tcrdlot"].ToString();
              model._HExp = DateUtil.ToDisplayDate(items["tcrdexp"], DateUtil.eLanguage.EN);
              model._Compound = items["compcd"].ToString();
              model._ComLot = items["Complot"].ToString();
              model._ComPlt = items["Comppllt"].ToString();
              model._ComExp = DateUtil.ToDisplayDate(items["Compexp"], DateUtil.eLanguage.EN);
              model._StdUsed = items["stdused"].ToString();
              model._Shift = items["shift"].ToString();
              model._Prddate = DateUtil.ToDisplayDate(items["prddate"], DateUtil.eLanguage.EN);
              model._Rollno = items["rollno"].ToString();
              model._Lenght = items["b_len"].ToString();
              model._Widgth = items["b_width"].ToString();
              model._Expired = DateUtil.ToDisplayDate(items["b_exp"], DateUtil.eLanguage.EN);
              model._50ds = items["ds50"].ToString();
              model._50os = items["os50"].ToString();
              model._50wd = items["wd50"].ToString();
              model._100ds = items["ds100"].ToString();
              model._100os = items["os100"].ToString();
              model._100wd = items["wd100"].ToString();
              model._200ds = items["ds200"].ToString();
              model._200os = items["os200"].ToString();
              model._200wd = items["wd200"].ToString();
              model._300ds = items["ds300"].ToString();
              model._300os = items["os300"].ToString();
              model._300wd = items["wd300"].ToString();
              model._400ds = items["ds400"].ToString();
              model._400ms = items["ms400"].ToString();
              model._400os = items["os400"].ToString();
              model._400wd = items["wd400"].ToString();
              model._Empname = items["empname"].ToString();
              list.Add(model);
          }
          return list;
      }

   }
}